<?php

    include(dirname(__FILE__)."/../global_conf.php");
    include(dirname(__FILE__)."/../config.php");
    require_once($site->classdir."/DBmanager.php");

    // Tipi primitivi di dato predefiniti
    $types=array();
    $types['integer']="INTEGER";
    $types['text']="TEXT";
    $types['string']="VARCHAR(256)";
    $types['date']="DATE";
    $types['image']="VARCHAR(128)";
    $types['video']="VARCHAR(64)";

    // Espressioni regolari per i tipi predefiniti
    $regexp=array();
    $regexp['integer']="/^\d+$/i";
    $regexp['text']="/.+/i"; //XXX
    $regexp['string']="/.+/i"; //XXX
    $regexp['date']="/.+/"; //XXX
    $regexp['image']="/.+/"; //XXX
    $regexp['video']="/.+/"; //XXX

    function get_table($tablename, $query) {
        $res = DBmanager::getInstance()->query($query);
        if (!$res)
            return false;
        $result=array();
        while ($row=$res->fetch_assoc()) {
            $result[]=$row;
        }

        return $result;
    }

    /*
     *  Questa funzione accetta come parametro un filtro, che puo` essere anche un array
     *  Se e` un array la chiave e` il nome dell'attributo
     *  Altrimenti una stringa nella forma attributo=valore
     */
    function get_table_values($tablename, $filter=null) {
        $query="SELECT * FROM ".$tablename;
        if ($filter == null)
            return get_table($tablename, $query);
        if (is_array($filter))
            return get_table($tablename, $query.implode("AND", $filter));

        return get_table($tablename, $query.$filter);
    }

    function get_table_description($tablename) {
        return get_table($tablename, "DESCRIBE ".$tablename);
    }

    function get_last_contents_inserted($published=null, $auth=null) {
        $query="SELECT * FROM core";
        if (!is_null($auth))
            $query.=" WHERE author=".$auth;
        if (!is_null($published)) {
            if (is_null($auth))
                $query.=" WHERE";
            else
                $query.=" AND";
            $query.=" published=".$published;
        }
        $query.=" ORDER BY inserted DESC LIMIT 0, 2";
        $res=DBmanager::getInstance()->query($query);
        $last_inserted=array();
        while ($row=$res->fetch_assoc()) {
            $last_inserted[$row['inserted']]=$row['title'];
        }

        return $last_inserted;
    }

    function get_table_list() {
        global $dbname;
        $res = DBmanager::getInstance()->query("SHOW TABLES");
        $tables = array();
        while ($row = $res->fetch_assoc())
                $tables[] = $row['Tables_in_'.$dbname];

        return $tables;
    }

    function input_sanitizing(&$cont) {
        global $site, $regexp;
        
        $istheredate=false;
        $attr_types=get_attributes_property("type");
        foreach ($cont as $key => $value) {
            $str=explode(":", $key);
            if (!$str || !isset($attr_types[$str[0]])) continue;
            if (!is_array($value)) {
                if (!preg_match($regexp[$attr_types[$str[0]]], $value)) {
                    $err="one or more fields invalid";
                    break;
                }
            }
            switch ($attr_types[$str[0]]) {
                case "date":
                    if (strcmp($str[count($str)-1], "day") == 0) {
                        $istheredate=$str[0]; // basta una sola volta, c'e` sempre
                        $day=$value;
                        unset($cont[$key]);
                    }
                    else if (strcmp($str[count($str)-1], "month") == 0) {
                        $month=$value;
                        unset($cont[$key]);
                    }
                    else if (strcmp($str[count($str)-1], "year") == 0) {
                        $year=$value;
                        unset($cont[$key]);
                    }
                    break;
                case "image": // FIXME: path dalla documentroot?????
                    if (!move_uploaded_file($value['tmp_name'], $site->uploaddir."/images/".$value['name']))
                        $err="cannot upload file";
                    else
                        unset($cont[$key]);
                    $cont[$str[0]]=$site->uploaddir."/images/".$value['name'];
                    break;
//                case "integer":
//                    if (!preg_match($regexp['integer'], $value))
//                        $err="not an integer";
//                    break;
            }
            if (isset($err)) break;
        }
        if (!isset($err)) {
            if ($istheredate)
                $cont[$istheredate]=$year."-".$month."-".$day;
        }
        else
            return $err;

        return true;
    }

    function insert_core_content($table, $cont) {
        $query = "INSERT INTO ".$table."(";
        $i=0;
        foreach ($cont as $key => $value) {
            if ($i >= 2) break;
            $query.=$key.",";
            ++$i;
        }
        $query.="author, content_type) VALUE(";
        $i=0;
        foreach ($cont as $key => $value) {
            if ($i >= 2) break;
            $query.="'".addslashes($value)."',";
            ++$i;
        }
        $query.=$_SESSION['logged'].",'".$cont['content_type']."')";
        DBmanager::getInstance()->query($query);
        // FIXME: che porcheria schifosa parte 2
        $query="SELECT MAX(id) as id FROM core";
        $res=DBmanager::getInstance()->query($query);

        return $res->fetch_assoc();
    }

    function insert_additional_content($nid, $type, $cont) {
        $query="INSERT INTO content_type_".$type."(";
        $attr_types=get_attributes_property("type");
        foreach ($cont as $key => $value) {
            $str=explode(":", $key);
            $query.=$str[0].",";
        }
        $query.="nid) VALUE(";
        foreach ($cont as $key => $value) {
            $query.="'".addslashes($value)."',";
        }
        $query.=$nid.")";
        if (DBmanager::getInstance()->query($query))
                return true;
        return false;
    }

    function update_content($nid, $content_type, $cont) {
        $query="UPDATE core SET ".
                "title='".addslashes($cont['title'])."',".
                "description='".addslashes($cont['description'])."',".
                "published=FALSE".
                " WHERE id=".$nid;
        if (!DBmanager::getInstance()->query($query))
            return false;
        unset($cont['title'], $cont['description']);
        $query="UPDATE content_type_".$content_type." SET ";
        $str_array=array();
        foreach ($cont as $key => $value) {
            $pieces=explode(":", $key);
            $str=$pieces[0]."='".$value."'";
            array_push($str_array, $str);
        }
        $query.=implode(",", $str_array)." WHERE nid=".$nid;

        return DBmanager::getInstance()->query($query);
    }

    function change_publishing($nid, $publish) {
        $query="UPDATE core SET published=".$publish." WHERE id=".$nid;
        DBmanager::getInstance()->query($query);
    }

    function get_author($nid) {
        $row=DBmanager::getInstance()->get_node($nid);
        if (!$row)
            return false;
        return $row['author'];
    }

    function get_author_by_id($id) {
        $query="SELECT usr FROM user WHERE id=".$id;
        $res=DBmanager::getInstance()->query($query);
        if (!$res || !($row=$res->fetch_assoc()))
                return false;
        return $row['usr'];
    }

    function delete_content($nid) {
        $row=DBmanager::getInstance()->get_node($nid, true);
        if (!$row)
                return false;
        $query="DELETE FROM core WHERE id=".$nid;
        $res=DBmanager::getInstance()->query($query);
        if ($res && !is_null($row['content_type'])) {
            $query="DELETE FROM content_type_".$row['content_type']." WHERE nid=".$nid;
            $res=DBmanager::getInstance()->query($query);
        }

        return $res;
    }

    function change_user_lang($id, $lang) {
        $query="UPDATE user SET preferred_lang='".$lang."' WHERE id=".$id;
        return DBmanager::getInstance()->query($query);
    }

    function get_sql_type($type) {
        global $types;

        foreach ($types as $key => $value) {
            if (strcmp($type, $key))
                    return $value;
        }
    }

    function create_datatype($sname, $lname) {
        $query="INSERT INTO node_type VALUE('".$sname."', '".addslashes($lname)."')";
        DBmanager::getInstance()->query($query);
        $query="CREATE TABLE content_type_".$sname."(
            nid INTEGER PRIMARY KEY REFERENCES core(id)
            )ENGINE=INNODB";
        DBmanager::getInstance()->query($query);
    }

    function add_table_attribute($table, $attribute) {
        $query="ALTER TABLE ".$table." ADD COLUMN ".$attribute['sname']." ".$attribute['sqltype'];
        $res=DBmanager::getInstance()->query($query);
        if ($res) {
            $query="INSERT INTO attribute_definition(short_name, full_name, type) VALUE('".
            $attribute['sname']."','".
            addslashes($attribute['fname'])."','".
            $attribute['type']."')";
            $res=DBmanager::getInstance()->query($query);
        }

        return $res;
    }
    
    function change_columns_order($table, $columns) {
        global $types;
        $query="ALTER TABLE content_type_".$table;
        $attr_types=get_attributes_property("type");
        $columns=array_reverse($columns, true);
        foreach ($columns as $column) {
            $query.=" MODIFY COLUMN ".$column." ".$types[$attr_types[$column]]." FIRST,";
        }
        $query.=" MODIFY COLUMN nid INTEGER FIRST";

        return DBmanager::getInstance()->query($query);
    }

    function get_attributes_property($property) {
        $query="SELECT * FROM attribute_definition";
        $res=DBmanager::getInstance()->query($query);
        $attr_types=array();
        while ($row = $res->fetch_assoc()) {
            if (strcmp($property, "type") == 0)
                $attr_types[$row['short_name']]=$row['type'];
            else if (strcmp($property, "full_name") == 0)
                $attr_types[$row['short_name']]=$row['full_name'];
        }

        return $attr_types;
    }

    function get_datatypes_names() {
        $query="SELECT * FROM node_type";
        $res = DBmanager::getInstance()->query($query);
        $assoc = array();
        while ($row = $res->fetch_assoc()) {
            $assoc[]=$row;
        }

        return $assoc;
    }

    function get_attributes($type){
        $query="DESCRIBE content_type_".$type;
        $res=DBmanager::getInstance()->query($query);
        $attributes=array();
        while ($row=$res->fetch_assoc()) {
            $attributes[]=$row;
        }
        unset($attributes[0]);

        return $attributes;
    }

    function change_credentials($usrid, $new_credentials) {
        $query="UPDATE user SET usr='".$new_credentials['username']."', passwd=MD5('".$new_credentials['password']."')
                WHERE id=".$usrid;
        DBmanager::getInstance()->query($query);
    }

    function insert_new_user($userdata) {
        $query="INSERT INTO user(";
        foreach ($userdata as $key => $value) {
            $query.=$key.",";
        }
        $query=substr_replace($query, "", -1);
        $query.=") VALUE(";
        foreach ($userdata as $key => $value) {
            if (strcmp($key, "passwd") == 0)
                    $query.="MD5('".$value."'),";
            else
                    $query.="'".$value."',";
        }
        $query=substr_replace($query, "", -1);
        $query.=")";
        DBmanager::getInstance()->query($query);
    }

    function get_usernames() {
        $query="SELECT id, usr FROM user";
        $res=DBmanager::getInstance()->query($query);
        if (!$res)
            return false;
        $usrnames=array();
        while ($row=$res->fetch_assoc()) {
            $usrnames[$row['id']]=$row['usr'];
        }

        return $usrnames;
    }

    function shorten_string($string, $maxsize=20) {
        if (strlen($string) <= $maxsize)
            return $string;
        else
            return substr($string, 0, $maxsize-3)."...";
    }

    function get_date_list($listname, $min_value, $max_value, $default=null) {
        $date="<select name=\"".$listname."\">";
        for ($i=$min_value; $i <= $max_value; $i++) {
            $date.="<option value=\"".$i."\"";
            if (isset($default) && $default == $i)
                $date.=" selected=\"selected\"";
            $date.=">".$i."</option>\n";
        }
        $date.="</select>";

        return $date;
    }
?>